﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using KPIS.GERP.GHRMS.DAL;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IBLL; 
using KPIS.DBM;

using KPIS.GERP.GHRMS.DALFactory;

namespace KPIS.GERP.GHRMS.BLL
{
    public class SalaryPromotionConfigBLL


    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        

        public void Insert_Promotions(int prm_year,int prm_period,string prm_name,decimal prm_quota
            , int emp_type_seq, int created_by)
        {


            try
            {

                dbManager.Open();
                dbManager.CreateParameters(8);
                dbManager.AddParameters(0, "@prm_year", prm_year);
                dbManager.AddParameters(1, "@prm_period", prm_period);
                dbManager.AddParameters(2, "@prm_name", prm_name);
                dbManager.AddParameters(3, "@emp_type_seq", emp_type_seq);
                dbManager.AddParameters(4, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(5, "@created_by", created_by);
                dbManager.AddParameters(6,"@created_when", DateTime.Today);
                dbManager.AddParameters(7,"@prm_quota", prm_quota);

                string strSQL;

                strSQL = " insert into hrm_prm_promotions(emp_type_seq,prm_year,prm_period,prm_quota,prm_name,record_status,created_by,created_when)"
                 + " values(@emp_type_seq,@prm_year,@prm_period,@prm_quota,@prm_name,@record_status,@created_by,@created_when)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                

            }
            catch (Exception ex)
            {
                throw new Exception("{Insert_Promotions}" + ex.Message);
            }
            finally
            {
                dbManager.Dispose();
            }

        }

        public DataTable selectPromotions(int prm_year, int emp_type_seq)
        {
            try
            {
                string sql; //แสดงข้อมูลในหน้าแรก
                sql = " select * "
                    + " from hrm_prm_promotions "
                    + " where "
                    + " prm_year =" + prm_year
                    + " and emp_type_seq = " + emp_type_seq
                    + " and record_status = '" + SystemInfo._ActiveRecordStatus + "'";

                //where emp_seq = " + emp_seq;
                DataSet ds;
                DataTable dt;
                ds = new DataSet();
                dbManager.Open();
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0];
                return dt;
            }
            catch (Exception ex)
            {

            }
            finally
            {
                dbManager.Dispose();
            }
            return null;
        }


        public string formatDate(string value)
        {

            //08/04/2553
            string tmp = null;
            string[] tmp2 = null;
            tmp2 = value.Split('/');
            tmp = tmp2[1] + "/" + tmp2[0] + "/" + Convert.ToString(int.Parse(tmp2[2]) - 543);

            return tmp;
        }

        public void UpdatePromotionsConfig(int prm_seq, int prm_year, int emp_type_seq, string prm_date, int prm_period, string prm_name, decimal prm_quota, int updated_by)
        {


            try
            {

                dbManager.Open();
                dbManager.CreateParameters(9);
                dbManager.AddParameters(0, "@prm_year", prm_year);
                dbManager.AddParameters(1, "@prm_period", prm_period);
                dbManager.AddParameters(2, "@emp_type_seq", emp_type_seq);
                dbManager.AddParameters(3, "@prm_date", DateTime.Parse(prm_date));
                dbManager.AddParameters(4, "@prm_name", prm_name);
                dbManager.AddParameters(5, "@prm_quota", prm_quota);
                dbManager.AddParameters(6, "@updated_by", updated_by);
                dbManager.AddParameters(7, "@updated_when", DateTime.Today);
                dbManager.AddParameters(8, "@prm_seq", prm_seq);

                string strSQL;

                strSQL = " update hrm_prm_promotions set"
                + " emp_type_seq=@emp_type_seq,prm_year=@prm_year,prm_date=@prm_date,prm_period=@prm_period,"
                + " prm_quota=@prm_quota,prm_name=@prm_name,updated_by=@updated_by,updated_when=@updated_when"
                + " where prm_seq=@prm_seq ";


                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);


            }
            catch (Exception ex)
            {
                throw new Exception("{Insert_Promotions}" + ex.Message);
            }
            finally
            {
                dbManager.Dispose();
            }

        }

        public void Insert_Promotions(int prm_year, int emp_type_seq, string prm_date, int prm_period, string prm_name, decimal prm_quota, int created_by)
        {


            try
            {
                dbManager.Open();
                dbManager.CreateParameters(9);
                dbManager.AddParameters(0, "@prm_year", prm_year);
                dbManager.AddParameters(1, "@prm_period", prm_period);
                dbManager.AddParameters(2, "@emp_type_seq", emp_type_seq);
                dbManager.AddParameters(3, "@prm_date", DateTime.Parse(prm_date));
                dbManager.AddParameters(4, "@prm_name", prm_name);
                dbManager.AddParameters(5, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(6, "@created_by", created_by);
                dbManager.AddParameters(7, "@created_when", DateTime.Today);
                dbManager.AddParameters(8, "@prm_quota", prm_quota);

                string strSQL;

                strSQL = " insert into hrm_prm_promotions(emp_type_seq,prm_year,prm_date,prm_period,prm_quota,prm_name,record_status,created_by,created_when)"
                 + " values(@emp_type_seq,@prm_year,@prm_date,@prm_period,@prm_quota,@prm_name,@record_status,@created_by,@created_when)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);


            }
            catch (Exception ex)
            {
                throw new Exception("{Insert_Promotions}" + ex.Message);
            }
            finally
            {
                dbManager.Dispose();
            }

        }


        public void Update( int inquire_subject_seq, int seq, int job_site_seq, string reason, int updated_by)

        {

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);
                dbManager.AddParameters(0, "@prm_seq", seq);
                dbManager.AddParameters(1, "@job_site_seq", job_site_seq);
                dbManager.AddParameters(2, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(3, "@updated_by", updated_by);
                dbManager.AddParameters(4, "@updated_when", DateTime.Today  );

                string sql;

                sql = " update hrm_prm_promotions "
                    + " reason=@reason ,"
                    + " record_status=@record_status ,"
                    + " updated_by=@updated_by ,"
                    + " updated_when=@updated_when "; 

                   // sql = " update hrm_es_inquire_dtls "
                   // + " set inquire_subject_seq=@inquire_subject_seq ,"
                   // + " seq=@seq ,"
                   // + " job_site_seq=@job_site_seq ,"
                   // + " reason=@reason ,"
                    //+ " record_status=@record_status ,"
                   // + " updated_by=@updated_by ,"
                    //+ " updated_when=@updated_when ";     

                dbManager.ExecuteNonQuery(CommandType.Text,sql) ;  

             }
            catch (Exception ex)
            {

            }
            finally
            {
                dbManager.Dispose();  
            }
        
        
        }

 
        public void Delete(int seq,int UpdatedBy)
        {
            try
            {

                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@updated_by", UpdatedBy);
                dbManager.AddParameters(2, "@seq", seq);

                string sql;
                //sql = " update  hrm_es_inquire_dtls set "
               // + " record_status=@record_status"
               // + ", updated_by = @updated_by"
               //   + " where inquire_dtls_seq = @seq"; 

                sql = " update  hrm_prm_promotions set "
                + " record_status=@record_status"
                + ", updated_by = @updated_by"
                + " where prm_seq = @seq"; 

                dbManager.ExecuteNonQuery(CommandType.Text, sql);
                
                //   dal.Delete(Sequence, UpdatedBy);
            }
            catch (Exception ex)
            {
                throw new Exception("");
            }
            finally
            {
                dbManager.Dispose();
            }
        }


        public DataTable select(int prm_year,int prm_period,int emp_type_seq)
        {
            try
            {
                string sql;
                sql = " select js.*, pp.*,pm.*,pm.job_site_seq as pm_job_site_seq"
                    + " from hrm_prm_promotions as pp, hrm_prm_managements as pm, hrm_mp_job_sites as js"
                    + " where pp.prm_seq = pm.prm_seq"
                    + " and pm.job_site_seq = js.job_site_seq"
                    + " and pp.prm_year =" + prm_year
                    + " and pp.prm_period = " + prm_period
                    + " and pp.emp_type_seq = " + emp_type_seq
                    + " and pm.record_status = '" + SystemInfo._ActiveRecordStatus + "'"; 

                //where emp_seq = " + emp_seq;
                DataSet ds;
                DataTable dt;
                ds = new DataSet();
                dbManager.Open();
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0];
                return dt;
            }
            catch (Exception ex)
            {

            }
                finally 
            {
                dbManager.Dispose();  
            }
            return null;
        }
        public int GetPromotionSequence(int prm_year, int prm_period, int emp_type_seq)
        {
            try
            {
                string sql;
                sql = " select pp.prm_seq"
                    + " from hrm_prm_promotions pp"
                    + " where pp.prm_year =" + prm_year
                    + " and pp.prm_period = " + prm_period
                    + " and pp.emp_type_seq = " + emp_type_seq
                    + " and pp.record_status = '" + SystemInfo._ActiveRecordStatus + "'";

                DataSet ds;
                ds = new DataSet();
                dbManager.Open();
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                return Convert.ToInt32(ds.Tables[0].Rows[0]["prm_seq"].ToString());
            }
            catch
            {
                return 0;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public DataTable select(string prm_job_site_seq, string class_seq)
        {
            try
            {
               string sql;
                //dbManager.CreateParameters(2);
                //dbManager.AddParameters(0, "@prm_job_site_seq", prm_job_site_seq);
                //dbManager.AddParameters(1, "@class_seq", class_seq);

                sql = " select *,pe.emp_seq as sequence, e.pos_seq as t_pos_seq, e.class_seq as t_class_seq, e.salary+pe.amount as t_new_salary, DATE_FORMAT(date_add(prm_date, interval 1 month),'%Y-%m-01') as t_strat_date   "
                    + " from hrm_prm_promotion_employees as pe"
                    + ", hrm_ps_employees as e"
                    + ", hrm_mp_positions as mp"
                    + ", hrm_mp_job_sites as js"
                    + ", hrm_pr_salary_groups as sg"
                    + ", hrm_prm_promotion_bases as pb"
                    + ", hrm_prm_promotions as p"
                    + " where pe.emp_seq = e.emp_seq"
                    + " and e.pos_seq = mp.pos_seq"
                    + " and e.working_job_site_seq = js.job_site_seq"
                    + " and e.class_seq = sg.class_seq"
                    + " and sg.sal_grp_seq = pb.sal_grp_seq"
                    + " and pe.prm_seq = p.prm_seq"
                    + " and pe.prm_job_site_seq in (" + prm_job_site_seq + ")"
                    + " and e.class_seq in (" + class_seq + ")";

                //where emp_seq = " + emp_seq;
                DataSet ds;
                DataTable dt;
                ds = new DataSet();
                dbManager.Open();
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0];
                return dt;
            }
            catch (Exception ex)
            {

            }
            finally
            {
                dbManager.Dispose();
            }
            return null;
        }



        public DataTable select()
        {
            try
            {
           
                string sql; //แสดงตอนทำการเพิ่มรายการ
                sql = " SELECT * FROM "
                    + " HRM_PRM_PROMOTIONS";
                DataSet ds;
                DataTable dt;
                ds = new DataSet();
                dbManager.Open();
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0];
                return dt;
            }
            catch (Exception ex)
            {

                return null;
                // throw new Exception("");
            }
            finally
            {
                dbManager.Dispose();  
            }
        }


     

   
    }
}
